package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import beans.ProjectItemBean;
import beans.TeamMembersBean;
import beans.TeamMemberItemBean;

public class TeamMemberDao {
	private DataAccess dataAccess = new DataAccess();
	
	public TeamMembersBean getTeamMembersForProject(int projectId) throws ClassNotFoundException, SQLException {
		TeamMembersBean teamMembersBean = new TeamMembersBean();
		
		String query = "SELECT TM.ID AS ID, TM.NAME AS NAME, TM.EMAIL AS EMAIL, TM.PASSWORD AS PASSWORD,"
				+ " R.NAME AS ROLE_NAME, P.NAME AS PROJECT_NAME"
				+ " FROM TEAM_MEMBERS TM, PROJECTS P, ROLES R"
				+ " WHERE TM.PROJECT_ID = P.ID"
				+ " AND TM.ROLE_ID = R.ID"
				+ " AND TM.PROJECT_ID = " + projectId
				+ " ORDER BY NAME";

		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			TeamMemberItemBean teamMemberItemBean = new TeamMemberItemBean();
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setPassword(rset.getString("PASSWORD"));
			teamMemberItemBean.setRoleName(rset.getString("ROLE_NAME"));
			teamMemberItemBean.setProjectName(rset.getString("PROJECT_NAME"));
			teamMembersBean.addRow(teamMemberItemBean);
		}
		dataAccess.closeConnection();
		
		return teamMembersBean;		
	}

	public TeamMembersBean getTeamMembersForSystem() throws ClassNotFoundException, SQLException {
		TeamMembersBean teamMembersBean = new TeamMembersBean();
		
		String query = "SELECT TM.ID AS ID, TM.NAME AS NAME, TM.EMAIL AS EMAIL, TM.PASSWORD AS PASSWORD,"
				+ " R.NAME AS ROLE_NAME, P.NAME AS PROJECT_NAME"
				+ " FROM TEAM_MEMBERS TM LEFT OUTER JOIN PROJECTS P ON TM.PROJECT_ID = P.ID"
				+ " JOIN ROLES R ON TM.ROLE_ID = R.ID"
				+ " ORDER BY NAME";

		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			TeamMemberItemBean teamMemberItemBean = new TeamMemberItemBean();
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setPassword(rset.getString("PASSWORD"));
			teamMemberItemBean.setRoleName(rset.getString("ROLE_NAME"));
			teamMemberItemBean.setProjectName(rset.getString("PROJECT_NAME"));
			teamMembersBean.addRow(teamMemberItemBean);
		}
		dataAccess.closeConnection();

		return teamMembersBean;		
	}

	public int insert(TeamMemberItemBean teamMemberItem) throws ClassNotFoundException, SQLException {
		String query = "INSERT INTO TEAM_MEMBERS (NAME, EMAIL, PASSWORD, PROJECT_ID, ROLE_ID)"
				+ " VALUES (?, ?, ?, ?, ?)";
		int projectId = teamMemberItem.getProjectId();
				
		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, teamMemberItem.getName());
		preparedStatement.setString(2, teamMemberItem.getEmail());
		preparedStatement.setString(3, teamMemberItem.getPassword());

		if (projectId == 0) {
			preparedStatement.setNull(4, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(4, projectId);
		}
		
		preparedStatement.setInt(5, teamMemberItem.getRoleId());

		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				teamMemberItem.setId(rset.getInt(1));
			}
			rset.close();
			
			// get project name and role name for screen
			
			ProjectsDao projectsDao = new ProjectsDao();
			ProjectItemBean projectItemBean = projectsDao.getProjectData(teamMemberItem.getProjectId());
			teamMemberItem.setProjectName(projectItemBean.getName());

			RolesDao rolesDao = new RolesDao();
			teamMemberItem.setRoleName(rolesDao.getRoleName(teamMemberItem.getRoleId()));
		}
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	public TeamMemberItemBean getProductOwner(int projectId) throws ClassNotFoundException, SQLException {
		TeamMemberItemBean teamMemberItemBean = new TeamMemberItemBean();
		String query = "SELECT TM.ID AS ID, TM.NAME AS NAME, TM.EMAIL AS EMAIL,"
				+ " TM.PROJECT_ID AS PROJECT_ID, TM.ROLE_ID AS ROLE_ID, R.NAME AS ROLE_NAME"
				+ " FROM TEAM_MEMBERS TM, ROLES R WHERE TM.ROLE_ID = R.ID AND TM.PROJECT_ID = " + projectId 
				+ " AND R.NAME = 'Product Owner'";
		
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setProjectId(rset.getInt("PROJECT_ID"));
			teamMemberItemBean.setRoleId(rset.getInt("ROLE_ID"));
			teamMemberItemBean.setRoleName(rset.getString("ROLE_NAME"));
		}
		dataAccess.closeConnection();
		
		return teamMemberItemBean;
	}
	
	public TeamMemberItemBean getTeamMemberInfo(int id) throws ClassNotFoundException, SQLException {
		TeamMemberItemBean teamMemberItemBean = new TeamMemberItemBean();
		String query = "SELECT TM.ID, TM.NAME, TM.EMAIL, TM.PASSWORD, TM.ROLE_ID, R.NAME, TM.PROJECT_ID, P.NAME"
				+ " FROM TEAM_MEMBERS TM LEFT OUTER JOIN PROJECTS P ON TM.PROJECT_ID = P.ID"
				+ " JOIN ROLES R ON TM.ROLE_ID = R.ID"
				+ " WHERE TM.ID = " + id;
				 
		ResultSet rset = dataAccess.getSelectResult(query);
		
		while (rset.next()) {
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setPassword(rset.getString("PASSWORD"));
			teamMemberItemBean.setProjectId(rset.getInt("PROJECT_ID"));
			teamMemberItemBean.setRoleId(rset.getInt("ROLE_ID"));
		}
		dataAccess.closeConnection();
		
		return teamMemberItemBean;
	}
	
	public TeamMemberItemBean getTeamMemberInfo(String email) throws ClassNotFoundException, SQLException {
		TeamMemberItemBean teamMemberItemBean = new TeamMemberItemBean();
		String query = "SELECT ID, NAME, EMAIL, PASSWORD, PROJECT_ID, ROLE_ID"
				+ " FROM TEAM_MEMBERS WHERE EMAIL = ?"; 

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, email);
		
		ResultSet rset = dataAccess.getSelectResult(preparedStatement);

		while (rset.next()) {
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setPassword(rset.getString("PASSWORD"));
			teamMemberItemBean.setProjectId(rset.getInt("PROJECT_ID"));
			teamMemberItemBean.setRoleId(rset.getInt("ROLE_ID"));
		}
		dataAccess.closeConnection();
			
		return teamMemberItemBean;
	}
	
	public TeamMemberItemBean getTeamMemberInfo(String username, String password) throws ClassNotFoundException, SQLException {
		TeamMemberItemBean teamMemberItemBean = null;
		
		// MySQL queries are not case-sensitive by default. Use the BINARY keyword to make it case sensitive.
		String query = "SELECT TM.ID, TM.NAME, TM.EMAIL, TM.PASSWORD, TM.ROLE_ID, R.NAME, TM.PROJECT_ID, P.NAME"
						+ " FROM TEAM_MEMBERS TM LEFT OUTER JOIN PROJECTS P ON TM.PROJECT_ID = P.ID"
						+ " JOIN ROLES R ON TM.ROLE_ID = R.ID"
					    + " WHERE BINARY EMAIL = ?"
					    + " AND BINARY PASSWORD = ?";
		
		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		
		preparedStatement.setString(1, username);
		preparedStatement.setString(2, password);

		ResultSet rset = dataAccess.getSelectResult(preparedStatement);

		while (rset.next()) {
			teamMemberItemBean = new TeamMemberItemBean();
			teamMemberItemBean.setId(rset.getInt("ID"));
			teamMemberItemBean.setName(rset.getString("NAME"));
			teamMemberItemBean.setEmail(rset.getString("EMAIL"));
			teamMemberItemBean.setPassword(rset.getString("PASSWORD"));
			teamMemberItemBean.setProjectId(rset.getInt("PROJECT_ID"));
			teamMemberItemBean.setProjectName(rset.getString("P.NAME"));
			teamMemberItemBean.setRoleId(rset.getInt("ROLE_ID"));
			teamMemberItemBean.setRoleName(rset.getString("R.NAME"));
		}
		dataAccess.closeConnection();
			
		return teamMemberItemBean;		
	}

	public int update(TeamMemberItemBean teamMemberItemBean) throws ClassNotFoundException, SQLException {
		String query;

		// if normal user is updating fields that are grayed out are not sent as request parameter to servlet
		// name, project_name and role_name are null; project_id and role_id are 0

		if (teamMemberItemBean.getName() == null) {
			query = "UPDATE TEAM_MEMBERS SET EMAIL=?, PASSWORD=? WHERE ID=?";
		}
		else {
			query = "UPDATE TEAM_MEMBERS SET EMAIL=?, PASSWORD=?, NAME=?, PROJECT_ID=?, ROLE_ID=? WHERE ID=?";
		}

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);

		preparedStatement.setString(1, teamMemberItemBean.getEmail());
		preparedStatement.setString(2, teamMemberItemBean.getPassword());

		if (teamMemberItemBean.getName() == null) {
			preparedStatement.setInt(3, teamMemberItemBean.getId());
		}
		else {
			preparedStatement.setString(3, teamMemberItemBean.getName());
			if (teamMemberItemBean.getProjectId() == 0) {
				preparedStatement.setNull(4, Types.INTEGER);
			}
			else {
				preparedStatement.setInt(4, teamMemberItemBean.getProjectId());
			}
			preparedStatement.setInt(5, teamMemberItemBean.getRoleId());
			preparedStatement.setInt(6, teamMemberItemBean.getId());
		}

		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get project_name and role_name
			ResultSet rset = dataAccess.getSelectResult("SELECT TM.NAME AS NAME, P.ID AS PROJECT_ID,"
					+ " P.NAME AS PROJECT_NAME, R.ID AS ROLE_ID, R.NAME as ROLE_NAME"
					+ " FROM TEAM_MEMBERS TM LEFT OUTER JOIN PROJECTS P ON TM.PROJECT_ID = P.ID"
					+ " JOIN ROLES R ON TM.ROLE_ID = R.ID"
					+ " WHERE TM.ID = " + teamMemberItemBean.getId());
			
			while (rset.next()) {
				if (teamMemberItemBean.getName() == null) {
					teamMemberItemBean.setName(rset.getString("NAME"));
					teamMemberItemBean.setRoleId(rset.getInt("ROLE_ID"));
					teamMemberItemBean.setProjectId(rset.getInt("PROJECT_ID"));
				}
				teamMemberItemBean.setRoleName(rset.getString("ROLE_NAME"));
				teamMemberItemBean.setProjectName(rset.getString("PROJECT_NAME"));
			}
		}
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}
	
	public int remove(int id) throws ClassNotFoundException, SQLException {
		int rowsDeleted = 0;
		
		// Uses ON DELETE SET NULL on TASK table to clear out assigned tasks
		String query = "DELETE FROM TEAM_MEMBERS WHERE ID=" + id;

		rowsDeleted = dataAccess.executeUpdate(query);
		dataAccess.closeConnection();
		
		return rowsDeleted;
	}

}
